﻿using System;
using System.Drawing;
using System.Windows.Forms;
using FarPoint.Win.Spread;
using FarPoint.Win;
using FarPoint.Win.Spread.Model;
using FarPoint.Win.Spread.CellType;

namespace GrapeCity.ExpenseBudget
{
    public partial class ExpenseBudget : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region ctor
        public ExpenseBudget()
        {
            InitializeComponent();

            Load += ExpenseBudget_Load;
        }
        #endregion

        #region Event handlers
        private void ExpenseBudget_Load(object sender, EventArgs e)
        {
            SetupSheet(fpSpread.ActiveSheet);
            SetSheetData(fpSpread.ActiveSheet);
            SetSheetRules(fpSpread.ActiveSheet);
        }
        #endregion

        #region Private methods
        private void SetupSheet(SheetView sheet)
        {
            SetSheetProperties(sheet);

            sheet.AddSpanCell(0, 1, 1, 4);
            sheet.AddSpanCell(0, 5, 1, 2);
            sheet.AddSpanCell(1, 1, 1, 6);
            sheet.AddSpanCell(2, 1, 1, 6);
            sheet.AddSpanCell(3, 1, 1, 2);
            sheet.AddSpanCell(16, 1, 1, 6);
            sheet.AddSpanCell(17, 1, 1, 6);

            sheet.Cells[0, 1].Value = "Expense Budget";
            sheet.Cells[0, 1].Font = new Font("Calibri", 28, FontStyle.Regular);
            sheet.Cells[0, 5].Value = "CONTOSO, 2013";
            sheet.Cells[0, 5].Font = new Font("Calibri", 11, FontStyle.Regular);
            sheet.Cells[0, 5].ForeColor = Color.FromArgb(64, 64, 64);
            sheet.Cells[0, 5].HorizontalAlignment = CellHorizontalAlignment.Right;
            sheet.Cells[0, 5].VerticalAlignment = CellVerticalAlignment.Bottom;

            sheet.Cells[3, 1].Value = "OPERATING BUDGET";
            sheet.Cells[3, 1].Font = new Font("Calibri", 13, FontStyle.Bold);
            sheet.Cells[3, 1].ForeColor = Color.FromArgb(64, 64, 64);

            sheet.Cells[4, 1, 15, 1].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[4, 1, 4, 6].Font = new Font("Calibri", 10, FontStyle.Bold);
            sheet.Cells[4, 1, 4, 6].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, false, true, false, false);
            sheet.Cells[15, 1, 15, 6].Font = new Font("Calibri", 10, FontStyle.Bold);
            sheet.Cells[15, 1, 15, 6].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, false, false, false, true);
            sheet.Cells[3, 1, 3, 2].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, true, true, true, false);
            sheet.Cells[4, 1, 15, 1].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, true, false, false, false);
            sheet.Cells[4, 1, 15, 1].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[4, 6, 15, 6].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, false, false, true, false);
            sheet.Cells[5, 1, 14, 6].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, false, false, false, true);

            sheet.Cells[1, 1, 1, 6].Border = new ComplexBorder(null, new ComplexBorderSide(Color.FromArgb(217, 217, 217), 1),
                null, new ComplexBorderSide(Color.FromArgb(217, 217, 217), 3));
            sheet.Cells[17, 1, 17, 6].Border = new ComplexBorder(null, new ComplexBorderSide(Color.FromArgb(217, 217, 217), 3),
                null, new ComplexBorderSide(Color.FromArgb(217, 217, 217), 1));

            sheet.Cells[4, 1].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, true, true, false, false);
            sheet.Cells[4, 6].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, false, true, true, false);
            sheet.Cells[15, 1].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, true, false, false, true);
            sheet.Cells[15, 6].Border = new LineBorder(Color.FromArgb(217, 217, 217), 1, false, false, true, true);

            CurrencyCellType currencyCell1 = new CurrencyCellType();
            currencyCell1.DecimalPlaces = 2;

            CurrencyCellType currencyCell2 = new CurrencyCellType();
            currencyCell2.DecimalPlaces = 2;
            currencyCell2.NegativeRed = true;

            PercentCellType percentCell1 = new PercentCellType();
            percentCell1.DecimalPlaces = 0;

            PercentCellType percentCell2 = new PercentCellType();
            percentCell2.DecimalPlaces = 1;
            percentCell2.NegativeRed = true;

            sheet.Cells[5, 3, 14, 5].CellType = currencyCell1;
            sheet.Cells[5, 6, 14, 6].CellType = percentCell1;
            sheet.Cells[15, 5].CellType = currencyCell2;
            sheet.Cells[15, 6].CellType = percentCell2;
        }

        private void SetSheetProperties(SheetView sheet)
        {
            sheet.DefaultStyle.VerticalAlignment = CellVerticalAlignment.Center;
            sheet.DefaultStyle.Font = new Font("Calibri", 10, FontStyle.Regular);
            sheet.DefaultStyle.ForeColor = Color.FromArgb(68, 84, 106);
            sheet.VerticalGridLine = new GridLine(GridLineType.None);
            sheet.HorizontalGridLine = new GridLine(GridLineType.None);

            sheet.SetRowHeight(0, 74);
            sheet.SetRowHeight(1, 8);
            sheet.SetRowHeight(2, 20);
            sheet.SetMultipleRowHeights(3, 14, 20);
            sheet.SetRowHeight(17, 8);

            int[] colWidths = { 18, 83, 111, 84, 85, 126, 129 };
            for (int col = 0; col < 7; ++col)
                sheet.SetColumnWidth(col, colWidths[col]);
        }

        private void SetSheetData(SheetView sheet)
        {
            object[,] data =
            {
                { "STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)" },
                { 0, "Advertising", 600, 545, 0, 0 },
                { 0, "Debts", 125, 150, 0, 0 },
                { 0, "Benefits", 100, 100, 0, 0 },
                { 0, "Supplies", 100, 90, 0, 0 },
                { 0, "Postage", 150, 145, 0, 0 },
                { 0, "Rent or mortgage", 1000, 1000, 0, 0 },
                { 0, "Sales expenses", 500, 630, 0, 0 },
                { 0, "Taxes", 350, 375, 0, 0 },
                { 0, "Utilities", 400, 370, 0, 0 },
                { 0, "Other", 500, 435, 0, 0 },
                { 0, "Total Expenses", 0, 0, 0, 0 }
            };

            for (int row = 4; row < 16; ++row)
            {
                for (int col = 1; col < 7; ++col)
                {
                    sheet.Cells[row, col].Value = data[row - 4, col - 1];
                }
            }
            sheet.FpSpread.AllowUserFormulas = true;
            TableView table = sheet.AddTable("tblOperatingExpense", 4, 1, 11, 6, TableStyle.TableStyleLight1);

            sheet.Cells[15, 3].Formula = "Sum($D$6:$D$15)";
            sheet.Cells[15, 4].Formula = "Sum($E$6:$E$15)";
            sheet.Cells[15, 5].Formula = "Sum($F$6:$F$15)";
            for (int row = 5; row < 15; ++row)
            {
                sheet.Cells[row, 1].Formula = "IFERROR([ACTUAL] / [BUDGET], \"\")";
                sheet.Cells[row, 5].Formula = "[BUDGET] - [ACTUAL]";
                sheet.Cells[row, 6].Formula = "IFERROR([DIFFERENCE ($)] / [BUDGET], \"\")";
            }
            sheet.Cells[15, 1].Formula = "$G$16";
            sheet.Cells[15, 6].Formula = "IFERROR($F$16 / $D$16, \"\")";
        }

        private void SetSheetRules(SheetView sheet)
        {
            IconSetConditionalFormattingRule iconSetRule1 = new IconSetConditionalFormattingRule(ConditionalFormattingIconSetStyle.ThreeCircledSymbols);
            iconSetRule1.ShowConditionalFormatOnly = true;
            iconSetRule1.IconRuleSet.Add(new ConditionalFormattingIconValue(-1, ConditionalFormattingValueType.Number, false));
            iconSetRule1.IconRuleSet.Add(new ConditionalFormattingIconValue(1, ConditionalFormattingValueType.Number, true));

            IconSetConditionalFormattingRule iconSetRule2 = new IconSetConditionalFormattingRule(ConditionalFormattingIconSetStyle.ThreeUnCircledSymbols);
            iconSetRule2.ShowConditionalFormatOnly = true;
            iconSetRule2.Reverse();
            iconSetRule2.IconRuleSet.Add(new ConditionalFormattingIconValue(-1, ConditionalFormattingValueType.Number, false));
            iconSetRule2.IconRuleSet.Add(new ConditionalFormattingIconValue(1, ConditionalFormattingValueType.Number, true));

            DatabarConditionalFormattingRule dataBarRule1 = new DatabarConditionalFormattingRule();
            dataBarRule1.Minimum = new ConditionalFormattingValue(ConditionalFormattingValueType.AutoMin);
            dataBarRule1.Maximum = new ConditionalFormattingValue(ConditionalFormattingValueType.AutoMax);
            dataBarRule1.FillColor = Color.Orange;
            dataBarRule1.Gradient = true;
            dataBarRule1.ShowBorder = true;
            dataBarRule1.BorderColor = Color.Orange;
            dataBarRule1.Direction = RightToLeft.Yes;

            DatabarConditionalFormattingRule dataBarRule2 = new DatabarConditionalFormattingRule();
            dataBarRule2.Minimum = new ConditionalFormattingValue(ConditionalFormattingValueType.AutoMin);
            dataBarRule2.Maximum = new ConditionalFormattingValue(ConditionalFormattingValueType.AutoMax);
            dataBarRule2.FillColor = Color.FromArgb(0, 138, 239);
            dataBarRule2.Gradient = true;
            dataBarRule2.ShowBorder = true;
            dataBarRule2.BorderColor = Color.FromArgb(0, 138, 239);

            DatabarConditionalFormattingRule dataBarRule3 = new DatabarConditionalFormattingRule();
            dataBarRule3.Minimum = new ConditionalFormattingValue(ConditionalFormattingValueType.Min);
            dataBarRule3.Maximum = new ConditionalFormattingValue(ConditionalFormattingValueType.Max);
            dataBarRule3.FillColor = Color.FromArgb(91, 155, 213);
            dataBarRule3.Gradient = true;
            dataBarRule3.ShowBorder = true;
            dataBarRule3.BorderColor = Color.FromArgb(91, 155, 213);
            dataBarRule3.UseNegativeFillColor = true;
            dataBarRule3.NegativeFillColor = Color.FromArgb(237, 125, 49);
            dataBarRule3.UseNegativeBorderColor = true;
            dataBarRule3.NegativeBorderColor = Color.FromArgb(237, 125, 49);
            dataBarRule3.AxisPosition = DataBarAxisPosition.Middle;

            sheet.SetConditionalFormatting(new CellRange[] { new CellRange(5, 4, 10, 1) }, dataBarRule1);
            sheet.SetConditionalFormatting(new CellRange[] { new CellRange(5, 3, 10, 1) }, dataBarRule2);
            sheet.SetConditionalFormatting(new CellRange[] { new CellRange(5, 6, 10, 1) }, dataBarRule3);
            sheet.SetConditionalFormatting(new CellRange[] { new CellRange(15, 1, 1, 1) }, iconSetRule1);
            sheet.SetConditionalFormatting(new CellRange[] { new CellRange(5, 1, 10, 1) }, iconSetRule2);
        }
        #endregion
    }
}


//
//ExpenseBudget.Designer.cs
//


namespace GrapeCity.ExpenseBudget
{
    partial class ExpenseBudget
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            // 
            // fpSpread
            // 
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread_Sheet1});
            this.fpSpread.Size = new System.Drawing.Size(477, 487);
            this.fpSpread.TabIndex = 0;
            // 
            // fpSpread_Sheet1
            // 
            this.fpSpread_Sheet1.Reset();
            this.fpSpread_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // ExpenseBudget
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "ExpenseBudget";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet1;
    }
}
